import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Properties;
import java.sql.*;

public class DBConnection {
	
	private Properties p;
	private Connection c;
	private Constants.DBType type;
	private static Statement stmt;

	public DBConnection(){
		type = null;
	}
	
	public DBConnection(Constants.DBType typeIn){
		type = typeIn;
	}
	
	public void setType(Constants.DBType typeIn){
		type = typeIn;
	}
	
	public Constants.DBType getType(){
		return type;
	}
	
	public void connect() {
		p = new Properties();
		
		String url = "jdbc:mysql://SQL09.FREEMYSQL.NET:3306/retailm";
		p.put("user", "retailm");
		p.put("password", "cs440retailm");
		p.put("characterEncoding", "ISO-8859-1");
		
		try {
            DriverManager.registerDriver(new com.mysql.jdbc.Driver());
			c = DriverManager.getConnection(url, p);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		//System.out.print("Connected as -> " + p.getProperty("user") + "\n");
	}
	
	public void disconnect(){
		try {
			c.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void insertRecord(Record r) {
		String query = "INSERT INTO ";
		String query2;
		switch (type){
		case EUSERS:
			query += "users ( clientID, login, password) values ('";
			User u = (User)r;
			query += u.getClientId() + "', '" + u.getLogin() + "', '" + u.getPassword() + "') ";
			break;
		case ELAYOUT:
			break;
		case EPRODUCTS:
			query += "sharedProducts ( productName, productCategory, ProductHeight, productWidth, productDepth, productPriority) values ('";
			Product sp = (Product)r;
			query += sp.getProductName() + "', '" + sp.getCategory() + "', '" + sp.getHeight() + "', '" + sp.getWidth() + "', '" + sp.getDepth() + "', '" + sp.getPriority() + "') ";
			break;
		default:
		}
		
		//System.out.println(query);
		
		try{
			stmt = c.createStatement();
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
						e.printStackTrace();
		}
		//need to assign this product data to the shared database, so we first have to get the id
		//then insert a product into shared with that id
		if (type == Constants.DBType.EPRODUCTS)
		{
			Product sp = (Product)r;
			ResultSet rs;
			int id = 0;
			query = "SELECT * from sharedProducts WHERE productName='" + sp.getProductName() + "'";
			//System.out.println(query);
			try{
				stmt = c.createStatement();
				rs = stmt.executeQuery(query);
				rs.last();
				id = rs.getInt("productId");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
							e.printStackTrace();
			}
			sp.setId(id);
			
			//System.out.println("newly inserted product Id: "+sp.getId());
						
			query2 = new String(query);
			query2 = "insert into privateProducts (productId, currentCount, monthlySales) values ('";
			query2 += sp.getId() + "', '" + sp.getCurrentCount() + "', '" + sp.getMonthlySales() + "') ";
			//System.out.println(query2);
			
			try{
				stmt = c.createStatement();
				stmt.executeUpdate(query2);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
							e.printStackTrace();
			}
		}
		else if(type == Constants.DBType.EUSERS)
		{
			User u = (User)r;
			ResultSet rs;
			int id = 0;
			query = "SELECT * from users WHERE login='" + u.getLogin() + "'";
			//System.out.println(query);
			try{
				stmt = c.createStatement();
				rs = stmt.executeQuery(query);
				rs.next();
				id = rs.getInt("userId");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
							e.printStackTrace();
			}
			u.setId(id);
		}
	}
	
	public void updateRecord(Record r) {
		String query = " UPDATE " + r.table + " SET ";
		String query2 = "UPDATE privateProducts SET ";
		switch (type){
		case EUSERS:
			User u = (User)r;
			query += "login='"+u.getLogin() + "', password='" + u.getPassword() + "', clientID="+u.getClientId();
			query += " WHERE userId="+u.getId();
			break;
		case ELAYOUT:
			break;
		case EPRODUCTS:
			Product pr = (Product)r;
			query += "productName='"+pr.getProductName()+ "', productCategory=" + pr.getCategory() + 
					", productHeight="+pr.getHeight() + ", productWidth="+pr.getWidth() + ", productDepth="+pr.getDepth() +
					", productPriority="+pr.getPriority();
			query += " WHERE productId="+pr.getId();
			query2 += "currentCount="+pr.getCurrentCount()+", monthlySales="+pr.getMonthlySales();
			query2 += " where productId="+pr.getId();
			break;
		default:
		}
		//System.out.println(query);
		//System.out.println(query2);

		
		try{
			stmt = c.createStatement();
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
						e.printStackTrace();
		}
		if(type==Constants.DBType.EPRODUCTS)
		{
			try{
				stmt = c.createStatement();
				stmt.executeUpdate(query2);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
							e.printStackTrace();
			}
		}
	}
	
	public Record[] getRecords(Map constraints) {
		Iterator entries = constraints.entrySet().iterator();
		String query = "SELECT * from ";
		
		switch(type)
		{
		case EUSERS:
			query += "users where ";
			while(entries.hasNext())
			{
				Entry entry = (Entry)entries.next();
				query += entry.getKey() + "='" + entry.getValue() + "' ";
				if(entries.hasNext())
				{
					query += "OR ";
				}
			}
			break;
		case EPRODUCTS:
			query = "SELECT sharedProducts.*, privateProducts.* " +
					"FROM privateProducts, sharedProducts " +
					"WHERE sharedProducts.productId=privateProducts.productId AND (";
			while(entries.hasNext())
			{
				Entry entry = (Entry)entries.next();
				query += entry.getKey() + "='" + entry.getValue() + "' ";
				if(entries.hasNext())
				{
					query += "OR ";
				}
			}
			query += ")";
			break;
		}
		
		//System.out.println(query);
		ResultSet rs;
		ArrayList<Integer> idList = new ArrayList<Integer>();
		ArrayList<Record> list = new ArrayList<Record>();
		
		try{
			stmt = c.createStatement();
			rs = stmt.executeQuery(query);

			if(Constants.DBType.EUSERS == type)
				while(rs.next())
				{
					User temp = new User(rs.getString("login"), rs.getString("password"), rs.getInt("clientId") );
					temp.setId(rs.getInt("userId"));
					if(!idList.contains(temp.getId()))
					{
						idList.add(temp.getId());
						list.add(temp);
					}
				}
			else if(Constants.DBType.EPRODUCTS == type)
				while(rs.next())
				{
					//System.out.println("Resultset: " + rs.toString());
					Product temp = new Product(rs.getInt("productCategory"), rs.getString("productName"), 
							rs.getInt("productHeight"), rs.getInt("productDepth"), rs.getInt("productWidth"),
							rs.getInt("productPriority"), rs.getInt("monthlySales"), rs.getInt("currentCount") );
					temp.setId(rs.getInt("productId"));
					temp.setPrice(rs.getFloat("price"));
					
					if(!idList.contains(temp.getId()))
					{
						idList.add(temp.getId());
						list.add(temp);
					}
				}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
						e.printStackTrace();
		}
		Record[] rtv = list.toArray(new Record[list.size()]);
		return rtv;
	}
	
}
